#!/usr/bin/python
# -*- coding: UTF-8 -*-

import os
import re
from openpyxl.reader.excel import load_workbook

from win32com.client import Dispatch


# 用openpyxl读取excel的load_workbook加上data_only参数，读取不到值的原因。
# 要用excel打开并保存才会读得到
# https://blog.51cto.com/antidarkness/1974684
#
def openAndSave(filename):
    xlApp = Dispatch("KET.Application")
    xlApp.Visible = False
    path = os.path.join(os.getcwd(), filename)
    xlBook = xlApp.Workbooks.Open(path)
    xlBook.Save()
    xlBook.Close()


def log(*val):
    print(f'[Fill Report] {" ".join(val)}')


class BaseReport():

    def __init__(self, template_path, year, month, report_name) -> None:
        self.template_path = template_path
        self.year = year
        self.month = month
        self.report_name = report_name
        self.wb = self.load_template()

    def load_template(self):
        return load_workbook(self.template_path)

    def this_month(self):
        return f'{self.year}年{self.month}月'

    def last_month(self):
        last_month = self.month - 1 if self.month > 1 else 12
        last_month_year = self.year if self.month > 1 else self.year - 1
        return f'{last_month_year}年{last_month}月'

    def parse_src_data(self, path, offset=0):
        '''从 xlsx 文件读取数据'''
        wb = load_workbook(path, data_only=True)
        ws = wb.active
        data = list(ws.values)[offset:]
        values = []
        for row in data:
            values.append(row)
        return values

    def set_src_files(self, path):
        self.src_files = [f'{path}/{file}' for file in os.listdir(path)]

    def generate(self):
        pass


class FlagReport(BaseReport):
    def __init__(self, template_path, year, month, report_name):
        super().__init__(template_path, year, month, report_name)

    def generate(self):
        log(self.report_name)
        log(self.last_month(), '->', self.this_month())

        modify_area = 'A3:Q20'
        ws = self.wb.active
        for src_file in self.src_files:
            log('处理', src_file)
            src_data = self.parse_src_data(src_file)
            title = src_data[0][0]
            week = re.search(r'(\d+)周', title).group(1)
            
            col_offset = 6
            for index, row in enumerate(ws[modify_area]):
                log(row[0].value, f'第{week}周')
                ranking = src_data[index + 3][5]
                flag = '⚑' if int(ranking) < 7 else ''
                row[int(week) - col_offset].value = flag
        
        for row in ws[modify_area]:
            count = 0
            for val in row:
                if val.value == '⚑':
                    print('flag', val.value)
                    count = count + 1
            row[16].value = count

        self.wb.save(f'{self.this_month()}报表.xlsx')


if __name__ == "__main__":
    report = FlagReport('flag.xlsx', 2020, 8, '高2021届高二下学期流动红旗统计')
    report.set_src_files('src')

    report.generate()
